import { AppDataSource } from './data.source'
import { User } from './entity/User'
import { Order } from './entity/Order'

// OneToMany
AppDataSource.initialize().then(async () => {
    const user1 = new User();
    let datestr = new Date().getTime();
    user1.firstName = datestr + 'firstName';
    user1.lastName = datestr + 'lastName';
    user1.isActive = datestr % 2 === 0 ? false : true;
    user1.age = datestr % 2 === 0 ? 18 : 20;
    user1.email = datestr + '@qq.com';
    await AppDataSource.manager.save(user1);

    const order1 = new Order();
    order1.product = datestr + 'product';
    order1.amount = 2;
    order1.user = user1;
    await AppDataSource.manager.save(order1)

    const order2 = new Order();
    order2.product = datestr + 'product2';
    order2.amount = 12;
    order2.user = user1;
    await AppDataSource.manager.save(order2)

    // 查询
    const queryBuilder = AppDataSource.manager.createQueryBuilder(User, 'user');
    const query = queryBuilder
        .select(['user.firstName'])
        .addSelect('user.lastName')
        .addSelect('SUM(order.amount)', 'totalAmount')   //汇总订单总数量
        .innerJoin('user.orders', 'order')      //用户表内链接订单表
        .where('user.isActive=:isActive', { isActive: true })
        .andWhere('order.amount=:amount', { amount: 2 })
        .groupBy('user.id') //按照user.id分组,按用户的id进行分组
        .orderBy('totalAmount', 'DESC')  //按订单的总数量倒叙排列
    const result = await query.getRawMany();
    console.log(result);

    // 查询至少买过一个商品的用户
    /*
    select * from user
    where user.id in (select `order`.userId from `order` where `order`.userId = user.id)
    */
    // 子查询
    const userRepository = AppDataSource.getRepository(User);
    // 查询user表
    const users = await userRepository.createQueryBuilder('user')
        .where(qb => {
            // 子查询条件
            const subQuery = qb.subQuery()
                .select('order.userId')
                .from(Order, 'order')
                .getQuery()
            return 'user.id in ' + subQuery
        })
        .getMany();
    // select * from user
    // where user.id in (select `order`.userId from `order`)
    console.log(users);
}).finally(() => {
    process.exit();
})